package cn.edu.scau.cmi.tangqingning.tools;

import java.io.File;
import java.io.IOException;
import java.util.List;

import cn.edu.scau.cmi.tangqingning.model.Group;
import cn.edu.scau.cmi.tangqingning.model.Student;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

/**
 * 该类是用来把excel表格的数据导入到数据库的。
 * @author 唐庆宁
 * @author 谭祖杰
 */
public class ExcelToDb {
	/**
	 * 把excel表格的学生信息数据导入到数据库。
	 * @param realPath String，excel表格的路径
	 * @return true 导入成功；false 导入失败
	 */
	public static boolean stuDataToDd(String realPath) {
		boolean flag = false;
		// 得到表格中所有的数据
		Student Student = new Student();// 定义一个学生对象
		Workbook rwb;
		try {
			rwb = Workbook.getWorkbook(new File(realPath));
			Sheet rs = rwb.getSheet("Sheet1");// 或者rwb.getSheet(0)
			int rows = rs.getRows();// 得到所有的行
			List listExcel = GetData.getAllStuPerInfoByExcel(realPath, Student);
			if (rows - 1 == listExcel.size()) {
				/* 得到数据库表中所有的数据 List listDb=StuService.getAllByDb(); */
				DbConnection db = new DbConnection();
				int i;
				for (i = 0; i < listExcel.size(); i++) {
					Student = (Student) listExcel.get(i);
					String stid = String.valueOf(Student.getStId());
					String stname = Student.getStName();
					String stsex = String.valueOf(Student.getStSex());
					String stAge = String.valueOf(Student.getStAge());
					String stTel = String.valueOf(Student.getStTel());
					String stDept = String.valueOf(Student.getStDept());
					String stAddress = String.valueOf(Student.getStAddress());
					String stgroup = String.valueOf(Student.getStGroup());
					String password = String.valueOf(Student.getPassword());

					if (!GetData.isExist(stid)) {
						// 不存在就添加
						String sql = "insert into stuperinfo (STID,STNAME,STSEX,STAGE,STTEL,STDEPT"
								+ ",STADDRESS,STGROUP,PASSWORD) values(?,?,?,?,?,?,?,?,?)";
						String[] str = new String[] { stid, stname, stsex, stAge, stTel, stDept, stAddress, stgroup,
								password + "" };
						if (db.addDataToDb(sql, str) != 1) {
							break;
						}
					} else {
						// 存在就更新
						String sql = "update stuperinfo set STNAME=?,STSEX=?,STAGE=?,STTEL=?,STDEPT=?,STADDRESS=?,STGROUP=?"
								+ ",PASSWORD=? where STID=?";
						String[] str = new String[] { stname, stsex, stAge, stTel, stDept, stAddress, stgroup, password,
								stid + "" };
						if (db.addDataToDb(sql, str) != 1) {
							break;
						}
					}
				}
				flag = true;
			} else {
				flag = false;
			}
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * 把excel表格的项目信息数据导入到数据库。
	 * @param realPath String，excel表格的路径
	 * @return flag
	 */
	public static boolean proDataToDd(String realPath) {
		boolean flag = false;
		// 得到表格中所有的数据
		Group group1 = new Group();
		Workbook rwb;
		try {
			rwb = Workbook.getWorkbook(new File(realPath));
			Sheet rs = rwb.getSheet("Sheet1");// 或者rwb.getSheet(0)
			int rows = rs.getRows();// 得到所有的行
			List listExcel = GetData.getAllProjectDataByExcel(realPath, group1);
			if (rows - 1 == listExcel.size()) {
				/* 得到数据库表中所有的数据 List listDb=StuService.getAllByDb(); */
				DbConnection db = new DbConnection();
				int i = 0;
				for (i = 0; i < listExcel.size(); i++) {
					group1 = (Group) listExcel.get(i);
					String groupnumber = String.valueOf(group1.getGroupNumber());
					if (!GetData.isExist1(groupnumber)) {
						// 不存在就添加
						// 时刻注意sql语句，不能少一个标点符号，不能少一个问号！！！！！
						// 时刻注意sql语句，不能少一个标点符号，不能少一个问号！！！！！
						// 时刻注意sql语句，不能少一个标点符号，不能少一个问号！！！！！
						// 时刻注意sql语句，不能少一个标点符号，不能少一个问号！！！！！
						// 再错一次扣一个鸡腿！！！
						String sql = "insert into projectdata (groupNumber,proIntroduction,proRequirement,proDivision,"
								+ "numberOfEvaluater,functionScore,interfaceScore,innovationScore,"
								+ "oralDefenseScore,averageScore) values(?,?,?,?,?,?,?,?,?,?)";
						String[] str = new String[] { String.valueOf(group1.getGroupNumber()),
								String.valueOf(group1.getProIntroduction()), String.valueOf(group1.getProRequirement()),
								String.valueOf(group1.getProDivision()), String.valueOf(group1.getNumberOfEvaluater()),
								String.valueOf(group1.getFunctionScore()), String.valueOf(group1.getInterfaceScore()),
								String.valueOf(group1.getInnovationScore()),
								String.valueOf(group1.getOralDefenseScore()),
								String.valueOf(group1.getAverageScore()) + "" };
						if (db.addDataToDb(sql, str) != 1) {
							break;
						}
					} else {
						// 存在就更新
						String sql = "update projectdata set proIntroduction=? ,proRequirement=? ,proDivision=?, numberOfEvaluater=? ,functionScore=?,"
								+ "interfaceScore=?,innovationScore=? , oralDefenseScore=? ,averageScore=? where groupNumber=?";
						String[] str = new String[] { String.valueOf(group1.getProIntroduction()) + "",
								String.valueOf(group1.getProRequirement()) + "",
								String.valueOf(group1.getProDivision()) + "",
								String.valueOf(group1.getNumberOfEvaluater()) + "",
								String.valueOf(group1.getFunctionScore()) + "",
								String.valueOf(group1.getInterfaceScore()) + "",
								String.valueOf(group1.getInnovationScore()) + "",
								String.valueOf(group1.getOralDefenseScore()) + "",
								String.valueOf(group1.getAverageScore()) + "",
								String.valueOf(group1.getGroupNumber()) + "" };
						if (db.addDataToDb(sql, str) != 1) {
							break;
						}
					}
				}
				flag = true;
			} else {
				flag = false;
			}
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
}
